﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Diagnostics;
using System.ServiceModel;
using System.Windows.Forms;

namespace CinemaDbLib
{
	public class Connection
	{
		private SqlConnection connection = null;
		private const string user = "DM73_3;";
		private const string pass = "MaaGodt;";
		private const string dbName = "DM73_3;";
		private const string address = "balder.ucn.dk;";
		private const string conString = "Data Source="+address+
			"Initial Catalog="+dbName+"Persist Security Info=True;"+
			"User ID="+user+"Password="+pass;

		public Connection()
		{
			connection = new SqlConnection(conString);
		}

		public void Open()
		{
			connection.Open();
		}
		public void Close()
		{
			connection.Close();
		}

		public SqlConnection getConnection()
		{
			return connection;
		}

		// Executes a non query operation and returns the number of affected rows
		public int Execute(string query)
		{
			try
			{
				SqlCommand command = connection.CreateCommand();
				command.CommandText = query;
				return command.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				MessageBox.Show(ex.Message);
				return -1;
			}
		}

		// Executes a query and returns the sql data reader
		public SqlDataReader Read(string query)
		{
			Logger.getInstance().LogMessage("DB -> Read: Query( " + query + " )");
			try
			{
				SqlCommand command = connection.CreateCommand();
				command.CommandText = query;
				return command.ExecuteReader();
			}
			catch (SqlException ex)
			{

				MessageBox.Show(ex.Message);
				//Console.Out.WriteLine("DB Layer Error Encountered: " + ex.Message);
				return null;
			}
		}

		// Execute a query and returns the value of [0, 0] [row, column]
		public Object ExecuteScalar(string query)
		{
			try
			{
				SqlCommand command = connection.CreateCommand();
				command.CommandText = query;
				return command.ExecuteScalar();
			}
			catch (SqlException ex)
			{
				Console.Out.WriteLine("DB Layer Error Encountered: " + ex.Message);
				return null;
			}
		}

		public int GetMaxId(string table, string column = "id")
		{
			int id;
			string query = "SELECT MAX(" + column + ") FROM " + table;
			try
			{
				this.Open();
				SqlDataReader reader = this.Read(query);
				reader.Read();
				id = reader.GetInt32(0);
				reader.Close();
			}
			catch (Exception)
			{
				id = 1;
			}
			finally
			{
				this.Close();
			}
			return id;
		}
	}
}
